package cn.kgc.news.dao.impl;

import cn.kgc.news.dao.BaseDao;
import cn.kgc.news.dao.NewsDao;
import cn.kgc.news.pojo.Category;
import cn.kgc.news.pojo.News;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NewsDaoImpl extends BaseDao implements NewsDao  {
    //查询所有新闻信息
    public List<News> getNewsList(){
        List<News> newsList=new ArrayList<>();
        //连接数据库
        try {
            if(getConnection()) {
                //sql操作
                String sql="SELECT * FROM news_detail";
                Object[]params={};
                rs = this.excuteSelect(sql,params);
                //取数据
                while(rs.next()){
                    News news=new News();
                    int id=rs.getInt("id");
                    int categoryId=rs.getInt("categoryId");
                    String title=rs.getString("title");
                    String summary=rs.getString("summary");
                    String content=rs.getString("content");
                    String author=rs.getString("author");
                    String picPath=rs.getString("picPath");
                    Timestamp createDate=rs.getTimestamp("createDate");
                    System.out.println(id+"\t"+categoryId+"\t"+title+"\t"+summary+"\t"+content+"\t"+author+"\t"+picPath+"\t"+createDate);
                    news.setId(id);
                    news.setCategoryId(categoryId);
                    news.setAuthor(author);
                    news.setContent(content);
                    news.setCreateDate(createDate);
                    news.setSummary(summary);
                    news.setPicPath(picPath);
                    news.setTitle(title);
                    newsList.add(news);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeConnection();
        }
        return newsList;
    }
    //根据标题查询新闻信息
    public List<News> getNewsListByTitle(String tit){
        List<News> newsList=new ArrayList<>();
        //连接数据库
        try {
            if(getConnection()) {
                //sql操作
                String sql = "SELECT * FROM news_detail WHERE title LIKE ?";
                Object[]params={"%" + tit + "%"};
                rs = this.excuteSelect(sql,params);   //更新：ps.executeUpdate()
                //取数据
                News news = new News();
                while (rs.next()) {
                    int id = rs.getInt("id");
                    int categoryId = rs.getInt("categoryId");
                    String title = rs.getString("title");
                    String summary = rs.getString("summary");
                    String content = rs.getString("content");
                    String author = rs.getString("author");
                    String picPath = rs.getString("picPath");
                    Timestamp createDate = rs.getTimestamp("createDate");
                    System.out.println(id + "\t" + categoryId + "\t" + title + "\t" + summary + "\t" + content + "\t" + author + "\t" + picPath + "\t" + createDate);
                    news.setId(id);
                    news.setCategoryId(categoryId);
                    news.setAuthor(author);
                    news.setContent(content);
                    news.setCreateDate(createDate);
                    news.setSummary(summary);
                    news.setPicPath(picPath);
                    news.setTitle(title);
                    newsList.add(news);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeConnection();
        }
        return newsList;
    }
    //修改新闻信息
    public int updateNews(News news){
        int ir=0;
        if(getConnection()){
            String sql="UPDATE `kgcnews`.`news_detail` SET `categoryId` = ? , `title` = ? , `summary` = ? , `content` = ?, `picPath` = ? ,`author` =?,`modifyDate` = ? WHERE `id` =?";
            try {
                Object[]params={news.getCategoryId(),news.getTitle(),news.getSummary(),news.getContent(),news.getPicPath(),news.getAuthor(),news.getModifyDate(),news.getId()};
                ir=this.excuteUpdateRows(sql,params);  //返回更新的行数
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                closeConnection();
            }
        }
        return ir;
    }
    //增加新闻信息
    public int insertNews(News news){
        int ir=0;
        if(getConnection()){
            String sql="INSERT INTO `kgcnews`.`news_detail` (`categoryId`, `title`, `summary`, `content`, `picPath`, `author`, `createDate`, `modifyDate`) VALUES (?,?, ?,?, ?,?, ?, ?); ";
            try {
                Object[]params={news.getCategoryId(),news.getTitle(),news.getSummary(),news.getContent(),news.getPicPath(),news.getAuthor(),new Date(),new Date()};
                ir=this.excuteUpdateRows(sql,params);  //返回更新的行数
                if(ir>0){
                    System.out.println(ir);
                    System.out.println("增加成功");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                closeConnection();
            }
        }
        return ir;
    }
    //根据Id删除新闻信息
    public int deleteNews(int id){
        int ir=0;
        if(getConnection()){
            String sql="delete from `kgcnews`.`news_detail` where id=?";
            try {
                Object[]params={id};
                ir=this.excuteUpdateRows(sql,params);  //返回更新的行数
                if(ir>0){
                    System.out.println(ir);
                    System.out.println("删除成功");
                }
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                closeConnection();
            }
        }
        return ir;
    }

    @Override
    public News getNewsById(int newsId) {
        News news = new News();
        //连接数据库
        try {
            if(getConnection()) {
                //sql操作
                String sql = "SELECT nd.*,nc.name FROM news_detail nd inner join news_category nc on nd.categoryId=nc.id " +
                        "WHERE nd.id=?";
                Object[]params={newsId};
                rs=this.excuteSelect(sql,params);  //返回更新的行数
                //取数据
                while (rs.next()) {
                    int id = rs.getInt("id");
                    int categoryId = rs.getInt("categoryId");
                    String title = rs.getString("title");
                    String summary = rs.getString("summary");
                    String content = rs.getString("content");
                    String author = rs.getString("author");
                    String picPath = rs.getString("picPath");
                    Timestamp modifyDate = rs.getTimestamp("modifyDate");
                    Timestamp createDate = rs.getTimestamp("createDate");
                    String name=rs.getString("name");
                    System.out.println(id + "\t" + categoryId + "\t" + title + "\t" + summary + "\t" + content + "\t" + author + "\t" + picPath + "\t" + createDate);
                    Category category=new Category();
                    category.setName(name);
                    news.setId(id);
                    news.setCategoryId(categoryId);
                    news.setAuthor(author);
                    news.setContent(content);
                    news.setCreateDate(createDate);
                    news.setSummary(summary);
                    news.setPicPath(picPath);
                    news.setModifyDate(modifyDate);
                    news.setTitle(title);
                    news.setCategory(category);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeConnection();
        }
        return news;
    }
}
